#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
数据库表结构创建脚本
项目：南华大学附属第一医院康复医学科管理系统
创建日期：2025-11-21
说明：此脚本用于在新的服务器环境中初始化数据库表结构
"""

import psycopg2
from psycopg2 import sql
import sys

# 数据库连接配置
DB_CONFIG = {
    'host': 'localhost',
    'port': 5432,
    'database': 'nanhua_rehab',
    'user': 'postgres',
    'password': '123456'  # 请修改为实际密码
}

# 所有建表SQL语句
CREATE_TABLES_SQL = """
-- =====================================================
-- 1. 医生表 (doctors)
-- =====================================================
DROP TABLE IF EXISTS doctors CASCADE;
CREATE TABLE doctors
(
    id            INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name          VARCHAR(100)                           NOT NULL,
    gender        VARCHAR(10),
    phone         VARCHAR(20)                            NOT NULL UNIQUE,
    password_hash TEXT                                   NOT NULL,
    title         VARCHAR(100),
    status        SMALLINT                 DEFAULT 1     NOT NULL,
    created_at    TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
    updated_at    TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL
);

COMMENT ON TABLE doctors IS '存储医生/管理员的基本信息和认证凭证';
COMMENT ON COLUMN doctors.id IS '医生的唯一标识符 (主键)';
COMMENT ON COLUMN doctors.phone IS '医生的电话号码，通常用作登录账号，必须唯一';
COMMENT ON COLUMN doctors.password_hash IS '存储由后端服务生成的密码哈希值，绝不存储明文密码';
COMMENT ON COLUMN doctors.title IS '医生的职称';
COMMENT ON COLUMN doctors.status IS '账号状态，例如 1 表示正常，0 表示禁用';

ALTER TABLE doctors OWNER TO postgres;
CREATE INDEX idx_doctors_name ON doctors (name);

-- =====================================================
-- 2. 医院表 (hospitals)
-- =====================================================
DROP TABLE IF EXISTS hospitals CASCADE;
CREATE TABLE hospitals
(
    id            SERIAL PRIMARY KEY,
    hospital_name VARCHAR(200)                                       NOT NULL UNIQUE,
    invite_code   VARCHAR(50)                                        NOT NULL UNIQUE,
    status        SMALLINT                 DEFAULT 1                 NOT NULL,
    created_at    TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at    TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL
);

COMMENT ON TABLE hospitals IS '医院表';
COMMENT ON COLUMN hospitals.id IS '医院ID';
COMMENT ON COLUMN hospitals.hospital_name IS '医院名称';
COMMENT ON COLUMN hospitals.invite_code IS '邀请码';
COMMENT ON COLUMN hospitals.status IS '状态 1-正常 0-禁用';
COMMENT ON COLUMN hospitals.created_at IS '创建时间';
COMMENT ON COLUMN hospitals.updated_at IS '更新时间';

ALTER TABLE hospitals OWNER TO postgres;
CREATE INDEX idx_hospitals_hospital_name ON hospitals (hospital_name);
CREATE INDEX idx_hospitals_invite_code ON hospitals (invite_code);

-- =====================================================
-- 3. 患者表 (patients)
-- =====================================================
DROP TABLE IF EXISTS patients CASCADE;
CREATE TABLE patients
(
    id                   INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name                 VARCHAR(100)                           NOT NULL,
    phone                VARCHAR(20) UNIQUE,
    id_card              VARCHAR(25) UNIQUE,
    gender               VARCHAR(10),
    age                  INTEGER
        CONSTRAINT patients_age_check CHECK (age >= 0),
    height               NUMERIC(5, 2)
        CONSTRAINT patients_height_check CHECK (height > 0),
    weight               NUMERIC(5, 2)
        CONSTRAINT patients_weight_check CHECK (weight > 0),
    handedness           VARCHAR(10),
    diabetes             BOOLEAN                  DEFAULT FALSE NOT NULL,
    hypertension         BOOLEAN                  DEFAULT FALSE NOT NULL,
    hyperlipidemia       BOOLEAN                  DEFAULT FALSE NOT NULL,
    stroke_history       BOOLEAN                  DEFAULT FALSE NOT NULL,
    heart_disease        BOOLEAN                  DEFAULT FALSE NOT NULL,
    atrial_fibrillation  BOOLEAN                  DEFAULT FALSE NOT NULL,
    sleep_apnea          BOOLEAN                  DEFAULT FALSE NOT NULL,
    has_smoking_history  BOOLEAN                  DEFAULT FALSE NOT NULL,
    smoking_amount       INTEGER
        CONSTRAINT patients_smoking_amount_check CHECK (smoking_amount >= 0),
    smoking_years        INTEGER
        CONSTRAINT patients_smoking_years_check CHECK (smoking_years >= 0),
    has_quit_smoking     BOOLEAN,
    has_drinking_history BOOLEAN                  DEFAULT FALSE NOT NULL,
    drinking_amount      INTEGER
        CONSTRAINT patients_drinking_amount_check CHECK (drinking_amount >= 0),
    drinking_years       INTEGER
        CONSTRAINT patients_drinking_years_check CHECK (drinking_years >= 0),
    has_quit_drinking    BOOLEAN,
    doctor_id            INTEGER,
    status               SMALLINT                 DEFAULT 1     NOT NULL,
    created_at           TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
    updated_at           TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
    avatar               TEXT
);

COMMENT ON TABLE patients IS '存储患者的基本信息、病史和生活习惯';
COMMENT ON COLUMN patients.doctor_id IS '逻辑外键，关联到 doctors 表的主键，由应用层保证其有效性';
COMMENT ON COLUMN patients.avatar IS '患者头像URL';

ALTER TABLE patients OWNER TO postgres;
CREATE INDEX idx_patients_doctor_id ON patients (doctor_id);
CREATE INDEX idx_patients_name ON patients (name);
CREATE INDEX idx_patients_phone ON patients (phone);

-- =====================================================
-- 4. 病例表 (medical_records)
-- =====================================================
DROP TABLE IF EXISTS medical_records CASCADE;
CREATE TABLE medical_records
(
    id                     INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    patient_id             INTEGER                                NOT NULL,
    patient_name           VARCHAR(100),
    initial_diagnosis      TEXT[]                                 NOT NULL,
    onset_date             TIMESTAMP WITH TIME ZONE,
    course_days            INTEGER
        CONSTRAINT medical_records_course_days_check CHECK (course_days >= 0),
    nihss_score            INTEGER
        CONSTRAINT medical_records_nihss_score_check CHECK (nihss_score >= 0),
    stroke_location        TEXT,
    paralysis_location     TEXT,
    has_surgery            BOOLEAN,
    surgery_types          TEXT[],
    has_artery_stenosis    BOOLEAN,
    artery_types           TEXT[],
    neck_vessel_ultrasound TEXT,
    has_language_disorder  BOOLEAN,
    disorder_type          TEXT,
    status                 SMALLINT                 DEFAULT 1     NOT NULL,
    created_at             TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
    updated_at             TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
    treatment_history      JSONB                    DEFAULT '[]'::JSONB
);

COMMENT ON TABLE medical_records IS '存储患者的详细病历信息';
COMMENT ON COLUMN medical_records.patient_id IS '逻辑外键，关联到 patients 表的主键';
COMMENT ON COLUMN medical_records.patient_name IS '冗余存储的患者姓名，以避免JOIN操作，应用层需保证数据同步';
COMMENT ON COLUMN medical_records.stroke_location IS '病变部位，以文本形式存储';
COMMENT ON COLUMN medical_records.treatment_history IS '治疗历史记录（JSON格式）';

ALTER TABLE medical_records OWNER TO postgres;
CREATE INDEX idx_medical_records_patient_id ON medical_records (patient_id);
CREATE INDEX idx_medical_records_patient_name ON medical_records (patient_name);
CREATE INDEX idx_medical_records_initial_diagnosis_gin ON medical_records USING GIN (initial_diagnosis);
CREATE INDEX idx_medical_records_stroke_location ON medical_records (stroke_location);

-- =====================================================
-- 5. 通知表 (notifications)
-- =====================================================
DROP TABLE IF EXISTS notifications CASCADE;
CREATE TABLE notifications
(
    id         INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    title      VARCHAR(200)                           NOT NULL,
    "desc"     TEXT                                   NOT NULL,
    date       VARCHAR(50)                            NOT NULL,
    is_read    BOOLEAN                  DEFAULT FALSE NOT NULL,
    status     SMALLINT                 DEFAULT 1     NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL
);

COMMENT ON TABLE notifications IS '存储系统通知信息';
COMMENT ON COLUMN notifications.id IS '通知的唯一标识符（主键）';
COMMENT ON COLUMN notifications.title IS '通知标题';
COMMENT ON COLUMN notifications."desc" IS '通知描述';
COMMENT ON COLUMN notifications.date IS '通知日期';
COMMENT ON COLUMN notifications.is_read IS '是否已读';
COMMENT ON COLUMN notifications.status IS '状态，1表示正常，0表示已删除';
COMMENT ON COLUMN notifications.created_at IS '创建时间';
COMMENT ON COLUMN notifications.updated_at IS '更新时间';

ALTER TABLE notifications OWNER TO postgres;
CREATE INDEX idx_notifications_status ON notifications (status);
CREATE INDEX idx_notifications_is_read ON notifications (is_read);
CREATE INDEX idx_notifications_created_at ON notifications (created_at DESC);

-- =====================================================
-- 6. 模板表 (templates)
-- =====================================================
DROP TABLE IF EXISTS templates CASCADE;
CREATE TABLE templates
(
    id              INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name            VARCHAR(255)                           NOT NULL,
    description     TEXT,
    category        VARCHAR(100),
    latest_version  INTEGER                  DEFAULT 1     NOT NULL,
    is_active       BOOLEAN                  DEFAULT TRUE  NOT NULL,
    created_by      INTEGER,
    created_at      TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
    updated_at      TIMESTAMP WITH TIME ZONE DEFAULT NOW() NOT NULL,
    version_history JSONB
);

COMMENT ON TABLE templates IS '存储评估模板的表';
COMMENT ON COLUMN templates.id IS '模板的唯一标识符';
COMMENT ON COLUMN templates.latest_version IS '模板当前的最新版本号，方便快速查询';
COMMENT ON COLUMN templates.version_history IS '存储所有历史版本的详细信息，包括 schema 和 uiSchema';

ALTER TABLE templates OWNER TO postgres;
CREATE INDEX idx_templates_name ON templates (name);
CREATE INDEX idx_templates_category ON templates (category);
CREATE INDEX idx_templates_version_history_gin ON templates USING GIN (version_history);

-- =====================================================
-- 7. 模板数据表 (template_data)
-- =====================================================
DROP TABLE IF EXISTS template_data CASCADE;
CREATE TABLE template_data
(
    id                SERIAL PRIMARY KEY,
    template_id       INTEGER                                            NOT NULL,
    patient_id        INTEGER                                            NOT NULL,
    patient_name      VARCHAR(100),
    medical_record_id INTEGER,
    form_data         JSONB                                              NOT NULL,
    status            SMALLINT                 DEFAULT 1                 NOT NULL,
    created_at        TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at        TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,
    template_name     VARCHAR(255),
    template_version  INTEGER                  DEFAULT 1                 NOT NULL,
    doctor_id         INTEGER                                            NOT NULL
);

COMMENT ON TABLE template_data IS '模板数据表';
COMMENT ON COLUMN template_data.id IS '模板数据ID';
COMMENT ON COLUMN template_data.template_id IS '模板表ID（逻辑外键）';
COMMENT ON COLUMN template_data.patient_id IS '患者ID（逻辑外键）';
COMMENT ON COLUMN template_data.patient_name IS '患者姓名';
COMMENT ON COLUMN template_data.medical_record_id IS '病历ID（逻辑外键，可选）';
COMMENT ON COLUMN template_data.form_data IS '模板表单数据（JSON格式）';
COMMENT ON COLUMN template_data.status IS '状态（1-已完成，0-待完成）';
COMMENT ON COLUMN template_data.created_at IS '创建时间';
COMMENT ON COLUMN template_data.updated_at IS '更新时间';
COMMENT ON COLUMN template_data.template_name IS '模板表名称（冗余字段，方便查询）';
COMMENT ON COLUMN template_data.template_version IS '模板版本号';
COMMENT ON COLUMN template_data.doctor_id IS '医生ID（逻辑外键）';

ALTER TABLE template_data OWNER TO postgres;
CREATE INDEX idx_template_data_template_id ON template_data (template_id);
CREATE INDEX idx_template_data_patient_id ON template_data (patient_id);
CREATE INDEX idx_template_data_medical_record_id ON template_data (medical_record_id);
CREATE INDEX idx_template_data_status ON template_data (status);
CREATE INDEX idx_template_data_created_at ON template_data (created_at DESC);
CREATE INDEX idx_template_data_template_name ON template_data (template_name);
CREATE INDEX idx_template_data_doctor_id ON template_data (doctor_id);
"""

# 初始管理员数据
INITIAL_ADMIN_SQL = """
-- =====================================================
-- 插入初始管理员账号
-- =====================================================
INSERT INTO doctors (
    name, gender, phone, password_hash, title, status, created_at, updated_at
) VALUES (
    '管理员',
    '男性',
    '17608430056',
    'pbkdf2:sha256:600000$admin_will_be_replaced$replace_this_with_real_hash',
    '主任医师',
    1,
    '2025-10-19 06:09:29.554000 +00:00',
    '2025-11-05 07:37:00.662706 +00:00'
) ON CONFLICT (phone) DO NOTHING;
"""


def create_tables():
    """创建所有数据库表"""
    conn = None
    cursor = None
    
    try:
        print("=" * 60)
        print("开始创建数据库表...")
        print("=" * 60)
        
        # 连接数据库
        print(f"\n>>> 正在连接数据库 {DB_CONFIG['database']}...")
        conn = psycopg2.connect(**DB_CONFIG)
        conn.autocommit = False
        cursor = conn.cursor()
        
        print("OK 数据库连接成功！\n")
        
        # 执行建表SQL
        print(">>> 正在创建表结构...")
        cursor.execute(CREATE_TABLES_SQL)
        
        # 提交事务
        conn.commit()
        print("OK 所有表创建成功！\n")
        
        # 插入初始管理员数据
        print(">>> 正在创建初始管理员账号...")
        try:
            # 生成 admin 密码的哈希值
            from werkzeug.security import generate_password_hash
            admin_password_hash = generate_password_hash('admin', method='pbkdf2:sha256', salt_length=16)
            
            cursor.execute("""
                INSERT INTO doctors (
                    name, gender, phone, password_hash, title, status, created_at, updated_at
                ) VALUES (
                    '管理员',
                    '男性',
                    '17608430056',
                    %s,
                    '主任医师',
                    1,
                    '2025-10-19 06:09:29.554000 +00:00',
                    NOW()
                ) ON CONFLICT (phone) DO NOTHING
                RETURNING id, name, phone;
            """, (admin_password_hash,))
            
            result = cursor.fetchone()
            if result:
                admin_id, admin_name, admin_phone = result
                print(f"OK 管理员账号创建成功！")
                print(f"   ID: {admin_id}")
                print(f"   姓名: {admin_name}")
                print(f"   手机号: {admin_phone}")
                print(f"   密码: admin")
            else:
                print("ℹ  管理员账号已存在，跳过创建")
            
            conn.commit()
        except ImportError:
            print("  警告：未安装 werkzeug，跳过管理员账号创建")
            print("   请运行：pip install werkzeug")
        except Exception as e:
            print(f"  警告：创建管理员账号失败 - {e}")
            conn.rollback()
        
        # 插入初始医院数据
        print(">>> 正在创建初始医院数据...")
        try:
            hospitals_data = [
                (1, '南华附一', '1234', 1, '2025-11-18 01:39:17.007545 +00:00', '2025-11-18 01:39:17.007545 +00:00'),
                (2, '南华附二', '123456', 1, '2025-11-18 01:39:17.007545 +00:00', '2025-11-18 01:39:17.007545 +00:00'),
            ]
            
            insert_count = 0
            for hospital_data in hospitals_data:
                cursor.execute("""
                    INSERT INTO hospitals (
                        id, hospital_name, invite_code, status, created_at, updated_at
                    ) VALUES (
                        %s, %s, %s, %s, %s, %s
                    ) ON CONFLICT (hospital_name) DO NOTHING
                    RETURNING id, hospital_name, invite_code;
                """, hospital_data)
                
                result = cursor.fetchone()
                if result:
                    hospital_id, hospital_name, invite_code = result
                    print(f"   ✓ {hospital_name}（邀请码：{invite_code}）")
                    insert_count += 1
            
            if insert_count > 0:
                print(f"OK 成功创建 {insert_count} 个医院！")
            else:
                print("ℹ  医院数据已存在，跳过创建")
            
            conn.commit()
        except Exception as e:
            print(f"  警告：创建医院数据失败 - {e}")
            conn.rollback()
        
        print()
        
        # 验证表是否创建成功
        print(">>> 验证表是否创建成功...")
        cursor.execute("""
            SELECT tablename 
            FROM pg_tables 
            WHERE schemaname = 'public' 
            ORDER BY tablename;
        """)
        
        tables = cursor.fetchall()
        print(f"\n>>> 已创建的表（共 {len(tables)} 个）：")
        for idx, (table_name,) in enumerate(tables, 1):
            print(f"   {idx}. {table_name}")
        
        print("\n" + "=" * 60)
        print(" 数据库表结构创建完成！")
        print("=" * 60)
        
    except psycopg2.Error as e:
        if conn:
            conn.rollback()
        print(f"\n 数据库错误：{e}")
        print(f"   错误代码：{e.pgcode}")
        print(f"   错误详情：{e.pgerror}")
        sys.exit(1)
        
    except Exception as e:
        if conn:
            conn.rollback()
        print(f"\n 发生错误：{e}")
        sys.exit(1)
        
    finally:
        # 关闭连接
        if cursor:
            cursor.close()
        if conn:
            conn.close()
            print("\n 数据库连接已关闭")


if __name__ == "__main__":
    print("""
╔════════════════════════════════════════════════════════════╗
║   南华大学附属第一医院康复医学科管理系统                   ║
║   数据库表结构创建脚本                                     ║
╚════════════════════════════════════════════════════════════╝
    """)
    
    # 检查配置
    if DB_CONFIG['password'] == 'your_password_here':
        print("  警告：请先在脚本中修改数据库密码！")
        print("   编辑 DB_CONFIG['password'] 字段\n")
        sys.exit(1)
    
    # 确认执行
    print(f" 数据库配置：")
    print(f"   主机：{DB_CONFIG['host']}:{DB_CONFIG['port']}")
    print(f"   数据库：{DB_CONFIG['database']}")
    print(f"   用户：{DB_CONFIG['user']}")
    print()
    
    response = input("  此操作将删除现有表并重新创建！是否继续？(yes/no): ")
    
    if response.lower() in ['yes', 'y']:
        create_tables()
    else:
        print("\n 操作已取消")
        sys.exit(0)

